-- @owner: @zhanghuan96
-- @date: 2024/11/14
-- @testpoint: GMS_XMLGEN.GETXMLTYPE参数类型测试，部分合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:建表;expect:成功
drop table if exists t_getxmltype0009;
create table t_getxmltype0009 (
    "integer" integer,
    "float" float,
    "numeric" numeric(20, 6),
    "boolean" boolean,
    "char" char(20),
    "varchar" varchar(20),
    "text" text,
    "blob" blob,
    "raw" raw,
    "date" date,
    "time" time,
    "timestamp" timestamp,
    "json" json,
    "varchar_array" varchar(20)[],
    "money" money,
    "bit" bit(3),
    c_xml xml
);
insert into t_getxmltype0009
values(
        1,
        1.23456,
        1.234567,
        true,
        '"''<>&char test',
        'varchar"''<>&test',
        'text test"''<>&',
        'ff',
        hextoraw('ABCD'),
        '2024-01-02',
        '18:01:02',
        '2024-02-03 19:03:04',
        '{"a" : 1, "b" : 2}',
        array['abc', '"''<>&', '你好'],
         '12.34'::float8::numeric::money,
         B'101',
          '<value>one</value>');
--step2:句柄id的类型错误;expect:报错
DECLARE
xml_cxt int128;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step3:参数1类型不可转换;expect:报错
DECLARE
xml_cxt json;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step4:参数1类型可转换;expect:成功
DECLARE
xml_cxt integer;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step5:参数1为number类型;expect:成功
DECLARE
xml_cxt number;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/

--step6:参数2类型为xml;expect:成功
DECLARE
xml_cxt integer;
xml_type xml;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step7:参数2为clob;expect:成功
DECLARE
xml_cxt integer;
xml_type clob;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step8:参数2为number;expect:报错
DECLARE
xml_cxt integer;
xml_type number;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step9:输出结果不转为xmltype;expect:成功
DECLARE
xml_cxt integer;
xml_type xmltype;
BEGIN
xml_cxt := gms_xmlgen.newcontext('select * from t_getxmltype0009');
xml_type := gms_xmlgen.getxmltype(xml_cxt,1);
RAISE INFO 'INFO:XML Output: %', xml_type;
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step10:清理环境;expect:成功
drop table if exists t_getxmltype0009;
drop extension if exists gms_xmlgen;